package cn.itlaobing.eshop.dao;/**
 * Created by 1223 on 2017/11/14.
 */

import cn.itlaobing.eshop.entity.GoodsDTO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 类名：
 * 描述：
 * 作者：陈志凯
 * 时间：2017/11/14
 */
public class GoodsDao extends BaseDao{
    public List<GoodsDTO> queryHotSale() throws SQLException{
        String sql="SELECT g.`id`,g.`price`,g.`good_name`,p.`picUrl` FROM itlb_goods g LEFT JOIN " +
                " (SELECT good_id,SUM(buy_count)sales_count " +
                " FROM itlb_order_details GROUP BY good_id)s " +
                " ON g.`id`=s.good_id " +
                " LEFT JOIN `itlb_goods_pic` p " +
                " ON g.`id`=p.`goodsId` " +
                " WHERE g.`status_line`=1 " +
                " ORDER BY s.sales_count limit 0,6 ";
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<GoodsDTO> beanListHandler=new BeanListHandler<>(GoodsDTO.class);
        return qr.query(sql,beanListHandler);
    }
    public List<GoodsDTO> queryNewSale() throws SQLException{
        String sql=" SELECT g.`id`,g.`price`,g.`good_name`,p.`picUrl` FROM itlb_goods g LEFT JOIN itlb_goods_pic p ON g.id=p.goodsId ORDER BY g.orders_weight DESC limit 0,6";
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<GoodsDTO> beanListHandler=new BeanListHandler<>(GoodsDTO.class);
        return qr.query(sql,beanListHandler);
    }
    /**
     * 单查
     * */
    public GoodsDTO queryById(Long id) throws SQLException{
        String sql="SELECT g.`id`,g.`price`,g.`good_name`,p.`picUrl` FROM itlb_goods g LEFT JOIN itlb_goods_pic p ON g.id=p.goodsId WHERE g.id=? ";
        QueryRunner runner=new QueryRunner(getDataSource());
        BeanHandler<GoodsDTO> beanHandler=new BeanHandler<>(GoodsDTO.class);
        return runner.query(sql,beanHandler,id);
    }
}
